from sqlite3 import connect
from collections import defaultdict
from sched_db.querybuilder import *
from itertools import groupby

# Finds the free timeslots
# "free" = the timeslot that has an unused room

#   dur: duration of the sought timeslot (in min: 90, 120, 180)
#   where: a condition on the columns of FreeRoom and Room
#
# Returns:
# { weekday : 
#    [ {btime: string in format of "0810"
#       btimmin: number of minutes from midnight
#       etimemin, 
#       day: MTWRF,
#       count:number of rooms available,
#       locations:all the rooms available} ]
# }

def free_timeslots(dbfile, dur, where=None):
  db = connect(dbfile)
  c = db.cursor()
  sql = """
    SELECT day, btime, location, campus, capacity
    FROM FreeRoom NATURAL JOIN Room
    WHERE duration = %d %s
    ORDER BY day, btime
  """ % (dur, "AND (%s)" % where if where else "")
  c.execute(sql)
  result = defaultdict(list)
  for ((day, btime), R) in groupby(c.fetchall(), key=lambda x: (x[0], x[1])):
    try:
      rooms = list(dict(location=x[2], campus=x[3], capacity=x[4]) for x in R)
      btimemin = int(btime[:2])*60+int(btime[2:])
      result[day].append(
        dict(btime=btime, btimemin=btimemin,
             etimemin=btimemin+dur,
             day=day,
             count=len(rooms),
             locations=rooms))
    except:
      pass
  c.close()
  db.close()
  return result

# Find the free room
#
#   dur: 90, 120, 180
#   btime: in the format of "0810"
#   day: MTWRF
#
# Returns:
#   list(location)

def free_locations(dbfile, dur, btime, day):
  with connect(dbfile) as db:
    c = db.cursor()
    sql = """ select location from FreeRoom
          where duration = ? and day = ? and btime = ?
          """
    c.execute(sql, (dur, day, btime))
    return [x[0] for x in c.fetchall()]

# Finds all the schedules of given courses grouped by
# day of the week
#
#   courses: list(course code)
#
# Returns:
#   { day:
#      [{ code, day, btime, etime, btimemin, etimemin }]
#   }

def occupied_timeslots(dbfile, courses):
  result = defaultdict(list)
  with connect(dbfile) as db:
    c = db.cursor()
    sql = """
      select code, day, btime, etime, %s as btimemin, %s as etimemin
      from Schedule
      where code in (%s)
    """ % (conv_to_minutes('btime'), conv_to_minutes('etime'),
    ",".join("'%s'" % x for x in courses))
    c.execute(sql)
    for (code, day, btime, etime, btimemin, etimemin) in c.fetchall():
      result[day].append(dict(code=code, day=day, btime=btime, etime=etime,
                         btimemin=btimemin, etimemin=etimemin))
    return result

